﻿//
//  Monkey Manager
//  Copyright (C) 2012 Victor Tereschenko
//
//  This program is free software: you can redistribute it and/or modify
//  it under the terms of the GNU General Public License as published by
//  the Free Software Foundation, either version 3 of the License, or
//  (at your option) any later version.
//
//  This program is distributed in the hope that it will be useful,
//  but WITHOUT ANY WARRANTY; without even the implied warranty of
//  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//  GNU General Public License for more details.
//
//  You should have received a copy of the GNU General Public License
//  along with this program.  If not, see <http://www.gnu.org/licenses/>.
//     
// ========================================================================

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace ManagerDB
{
    partial class Database
    {
        class Builder
        {
            public Builder(SQLiteConnection connection)
            {
                mConnection = connection;
            }
            SQLiteConnection mConnection;

            /// <summary>
            /// Corrects tables structure.
            /// </summary>
            public void CorrectTables()
            {
                executeCommand(mCreateFilesTable);
                executeCommand(mCreateTagsTable);
                executeCommand(mCreateTagTypesTable);
                executeCommand(mCreateFileTagsTable);
                executeCommand(mCreateMetadata);
                var version = getDBVersion();
                if (version == null)
                {
                    SQLiteCommand command = new SQLiteCommand("INSERT INTO metadata(field, value) VALUES(@field, @val);", mConnection);
                    command.Parameters.Add(new SQLiteParameter("@field", "version"));
                    command.Parameters.Add(new SQLiteParameter("@val", "1.0"));
                    command.ExecuteNonQuery();
                    executeCommand("ALTER TABLE files ADD TTH TEXT;");
                }
            }

            private string getDBVersion()
            {
                SQLiteCommand command = new SQLiteCommand("SELECT value FROM metadata WHERE field = @name;", mConnection);
                command.Parameters.Add(new SQLiteParameter("@name", "version"));
                var reader = command.ExecuteReader();
                if (reader.Read())
                {
                    var version = reader["value"];
                    reader.Close();
                    return (string)version;
                }
                reader.Close();
                return null;
            }

            private void executeCommand(string command)
            {
                SQLiteCommand createFilesCommand = new SQLiteCommand(command, mConnection);
                createFilesCommand.CommandType = System.Data.CommandType.Text;
                createFilesCommand.ExecuteNonQuery();
            }

            SQLiteDataReader getFileTags(long tagId, SQLiteConnection connection)
            {
                SQLiteCommand command = new SQLiteCommand("SELECT fileId FROM fileTags WHERE tagId = @tagId;", connection);
                command.Parameters.Add(new SQLiteParameter("@tagId", tagId));
                return command.ExecuteReader();
            }

            #region SQL
            static string mCreateMetadata = "CREATE TABLE IF NOT EXISTS metadata(field TEXT, value TEXT);";

            static string mCreateFilesTable = "CREATE TABLE IF NOT EXISTS files(" +
                "fileName TEXT," +
                "year INTEGER," +
                "day INTEGER," +
                "month INTEGER," +
                "title TEXT," +
                "description TEXT," +
                "count REAL DEFAULT 0.0," +
                "lastPosition REAL," +
                "locationID INTEGER" +
                "TTH TEXT" +
                ");";

            static string mCreateTagsTable = "CREATE TABLE IF NOT EXISTS tags(" +
                "name TEXT," +
                "nameId TEXT," +
                "typeId INTEGER REFERENCES tagTypes(rowid) ON DELETE SET NULL" +
                ");";

            static string mCreateTagTypesTable = "CREATE TABLE IF NOT EXISTS tagTypes(" +
                "name TEXT" +
                ");";

            static string mCreateFileTagsTable = "CREATE TABLE IF NOT EXISTS fileTags(" +
                "fileId INTEGER REFERENCES files(rowid) ON DELETE CASCADE," +
                "tagId INTEGER REFERENCES tags(rowid) ON DELETE CASCADE" +
                ");";
            #endregion

            /// <summary>
            /// Adds a tag.
            /// </summary>
            /// <param name="tag">Tag to add</param>
            void addTag(string tag)
            {
                long tagID = getTagID(tag);
                if (tagID != -1)
                    return;

                SQLiteCommand command = new SQLiteCommand("INSERT INTO tags(name, nameId) VALUES(@name, @nameId)", mConnection);
                command.Parameters.Add(new SQLiteParameter("@name", tag));
                command.Parameters.Add(new SQLiteParameter("@nameId", tag.ToLower()));
                command.ExecuteNonQuery();
            }

            /// <summary>
            /// Adds file tag.
            /// </summary>
            /// <param name="fileID">File ID.</param>
            /// <param name="tag">Tag name.</param>
            public void addFileTag(long fileID, string tag)
            {
                long tagID = getTagID(tag);
                if (tagID == -1)
                {
                    addTag(tag);
                    tagID = getTagID(tag);
                    if (tagID == -1)
                        throw new Exception("DB error");
                }

                SQLiteCommand command = new SQLiteCommand("INSERT INTO fileTags(tagId, fileId) VALUES(@tagId, @fileId)", mConnection);
                command.Parameters.Add(new SQLiteParameter("@tagId", tagID));
                command.Parameters.Add(new SQLiteParameter("@fileId", fileID));
                command.ExecuteNonQuery();
            }

            /// <summary>
            /// Removes file tag.
            /// </summary>
            /// <param name="fileID">File ID.</param>
            /// <param name="tag">Tag name.</param>
            public void removeFileTag(long fileID, string tag)
            {
                long tagID = getTagID(tag);
                if (tagID == -1)
                    return;

                SQLiteCommand command = new SQLiteCommand("DELETE FROM fileTags WHERE tagId=@tagId AND fileId=@fileId", mConnection);
                command.Parameters.Add(new SQLiteParameter("@tagId", tagID));
                command.Parameters.Add(new SQLiteParameter("@fileId", fileID));
                command.ExecuteNonQuery();
            }

            /// <summary>
            /// Gets tag ID by name.
            /// </summary>
            /// <param name="tagName">Name of the tag.</param>
            /// <returns>Tag ID. -1 is returned if the tag with specified name is not found.</returns>
            public long getTagID(string tagName)
            {
                SQLiteCommand command = new SQLiteCommand("SELECT rowid FROM tags WHERE nameId=@nameId", mConnection);
                command.Parameters.Add(new SQLiteParameter("@nameId", tagName.ToLower()));
                object res = command.ExecuteScalar();
                if (res == null || res == DBNull.Value)
                    return -1;

                return (long)res;
            }
        }
    }
}
